declare @EndDate datetime;
set @EndDate = CONVERT(datetime,'2018-01-01',121);
SELECT P.ProjectId,
  P.ProjectDisplayCode,
  P.ProjectTitle,
  ISNULL(P.BookPrice, 0) BookPrice,
  ISNULL(SP.PrintSheetAmount, 0) AS SheetCount,
  ISNULL(P.PrintingAmount, 0) AS PrintingAmount,
  ISNULL(ROUND(SP.PrintSheetAmount * P.PrintingAmount, 3), 0) AS SheetCountSUM,
  ISNULL(ROUND(P.BOOKPRICE * P.PrintingAmount, 2), 0) AS SumListInCome,
  ISNULL(POF.CheckedFeeTotal, 0) - ISNULL(CF.COSTSUM, 0) CheckedFeeTotal,
  ISNULL(CA.AverageUnitPrice, 0) AverageUnitPrice_6,
  ISNULL(CA.CFPrintAmount, 0) - ISNULL(PCI.CheckInAmount, 0) CFPrintAmount,
  ISNULL(CA.CFPrintCost, 0) - ISNULL(PCI.CheckInCost, 0) CFPrintCost,
  (ISNULL(P.PrintingAmount, 0) - ISNULL(CA.CFPrintAmount, 0) +
   ISNULL(PCI.CheckInAmount, 0)) - ISNULL(cacba.CaudaBookAmount, 0) AS WaitCFPrintAmountCost,
  (ISNULL(POF.CheckedFeeTotal, 0) - ISNULL(CA.CFPrintCost, 0) -
   ISNULL(CA.AdjustCost, 0) - ISNULL(CF.COSTSUM, 0) +
   ISNULL(PCI.CheckInCost, 0) + ISNULL(AMI.PrintCostMargin, 0)) AS WaitCFCheckageFee,
  ISNULL(CA.OtehrInOutCost, 0) - ISNULL(OOI.OtherOutInCost, 0) OtehrInOutCost,
  ISNULL(CA.OtherInOutAmount, 0) - ISNULL(OOI.OtherOutInAmount, 0) OtherInOutAmount,
  ISNULL(CA.CarryForwordCost, 0) - ISNULL(CFC.CarryForwordCost, 0) CarryForwordCost,
  ISNULL(CA.CarryForwordTotal, 0) - ISNULL(CFC.CarryForwordAmount, 0) CarryForwordTotal,
  ISNULL(CA.PrintCostMargin, 0) PrintCostMargin,
  ISNULL(CA.StorageCostMargin, 0) StorageCostMargin,
  ISNULL(CA.SalesCostMargin, 0) SalesCostMargin,
  ISNULL(CA.AdjustCost, 0) - ISNULL(AMI.PrintCostMargin, 0) AdjustCost,
  ISNULL(CA.AdjustStorageCost, 0) - ISNULL(AMI.StorageCostMargin, 0) AdjustStorageCost,
  ISNULL(CA.AdjustFormCost, 0) - ISNULL(AMI.SalesCostMargin, 0) AdjustFormCost,
  ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +
  ISNULL(CFC.CarryForwordAmount, 0) + ISNULL(OOI.OtherOutInAmount, 0) WaitCFTotal,
  (ISNULL(CA.CFPrintCost, 0) + ISNULL(CA.AdjustStorageCost, 0) -
   ISNULL(CA.OtehrInOutCost, 0) - ISNULL(CA.CarryForwordCost, 0) -
   ISNULL(PCI.CheckInCost, 0) - ISNULL(AMI.StorageCostMargin, 0) +
   ISNULL(CFC.CarryForwordCost, 0) + ISNULL(OOI.OtherOutInCost, 0)) AS WaitCarryForwordCost,
  ISNULL(B.BookPrice *
         (ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +
          ISNULL(CFC.CarryForwordAmount, 0) +
          ISNULL(OOI.OtherOutInAmount, 0)),
         0) CCPListInCome,
  ISNULL(POF.BalancedFeeTotal, 0) BalancedFeeTotal,
  ISNULL(POF.UnBalancedFeeTotal, 0) UnBalancedFeeTotal,
  ISNULL(FEE. [ 稿费 / 校订费 / 审稿费 ], 0) RemunerationFees,
  ISNULL(FEE. [ 租型费 ], 0) HiredFees,
  ISNULL(FEE. [ 纸张费 ], 0) SheetFees,
  ISNULL(FEE. [ 装帧纸张费 ], 0) MaterialFees,
  ISNULL(FEE. [ 制版费 ], 0) MakeFees,
  ISNULL(FEE. [ 印刷费 ], 0) PrintingFees,
  ISNULL(FEE. [ 装订费 ], 0) BindFees,
  ISNULL(FEE. [ 其他直接费用 ], 0) PublishLossFees,
  ISNULL(FEE. [ 编录经费 ], 0) AnnalFees,
  ISNULL(FEE. [ 管理费 ], 0) ManageFees,
  ISNULL(FEE. [ 财务费 ], 0) FinanceFees,
  ISNULL(FEE. [ 销售费用 ], 0) SellFees
FROM (SELECT *,
        (CASE
         WHEN ParentsProjectId IS NOT NULL AND ParentsProjectId > 0 THEN
           ParentsProjectId
         ELSE
           ProjectId
         END) AS BProjectId
      FROM Project) AS P
  left join PrintersBalance pb
    on p.ProjectId = pb.ProjectId
  LEFT JOIN (SELECT ca.ProjectId, CaudaBookAmount
             FROM CostAnalysis ca
               INNER JOIN PrintersBalance pb
                 ON ca.ProjectId = pb.ProjectId
             WHERE pb.StorageOverDate < @enddate
                   AND pb.StorageOverState = '44') cacba
    ON cacba.ProjectId = p.ProjectId
  LEFT OUTER JOIN Book AS B
    ON P.BookId = B.BookId
  LEFT OUTER JOIN ISBN
    ON P.ProjectId = ISBN.ProjectId
  LEFT OUTER JOIN EditorsBalance C
    ON P.ProjectId = C.ProjectId
  LEFT OUTER JOIN SendToPrinting SP
    ON P.ProjectId = SP.ProjectId
  LEFT OUTER JOIN (SELECT PROJECTID,
                     ISNULL(SUM(CHECKEDFEE), 0) AS CheckedFeeTotal,
                     ISNULL(SUM(BalancedFee), 0) AS BalancedFeeTotal,
                     ISNULL(SUM(UnBalancedFee), 0) AS UnBalancedFeeTotal
                   FROM ProjectOccuredFee
                   GROUP BY PROJECTID) POF
    ON P.ProjectId = POF.ProjectId
  LEFT OUTER JOIN CostAnalysis CA
    ON P.ProjectId = CA.ProjectId
  LEFT OUTER JOIN (SELECT ProjectId, ISNULL(SUM(CostSum), 0) CostSum
                   FROM (SELECT pcf.ProjectId,
                           ISNULL(CheckageThis, 0) - ISNULL(TaxSum, 0) CostSum
                         FROM ProjectCheckageFee pcf, Form F
                         WHERE F.AKTime >= @enddate
                               AND pcf.CheckageId = F.FormId
                         UNION ALL
                         SELECT ma.ProjectId, MaterialSum
                         FROM MaterialApply MA, FORM F
                         WHERE F.AKTime >= @enddate
                               AND MA.MaterialApplyId = F.FormId
                         UNION ALL
                         SELECT fsi.ProjectId, SeperateSum
                         FROM FeeSeperateItem FSI, FORM F
                         WHERE f.AKTime >= @enddate
                               AND FSI.SeperateId = F.FormId) C
                   GROUP BY ProjectId) cf
    ON P.ProjectId = cf.ProjectId
  LEFT OUTER JOIN (SELECT ProjectId,
                     SUM(CheckInAmount) CheckInAmount,
                     SUM(SellIncome) CheckInCost
                   FROM (SELECT PrintCheckInId,
                           ProjectId,
                           CheckInAmount,
                           SellIncome
                         FROM PrintCheckIn
                         WHERE ProjectId IS NOT NULL
                         UNION ALL
                         SELECT pci.PrintCheckInId,
                           Pci.ProjectId,
                           Pci.CheckInAmount,
                           Pci.SellIncome
                         FROM PrintCheckIn pci, PrintForm pf
                         WHERE pci.PrintFormId = pf.PrintFormId
                               AND pci.ProjectId IS NULL
                         UNION ALL
                         SELECT pci.PrintCheckInId,
                           cf.ProjectId,
                           Pci.CheckInAmount,
                           Pci.SellIncome
                         FROM PrintCheckIn pci, ColligateForm cf
                         WHERE pci.PrintFormId = cf.ColligateFormId
                               AND pci.ProjectId IS NULL) pci,
                     Form F
                   WHERE pci.PrintCheckInId = F.FormId
                         AND F.AKTime >= @enddate
                   GROUP BY ProjectId) pci
    ON P.ProjectId = pci.ProjectId
  LEFT OUTER JOIN (SELECT ProjectId,
                     SUM(PrintCostMargin) PrintCostMargin,
                     SUM(StorageCostMargin) StorageCostMargin,
                     SUM(SalesCostMargin) SalesCostMargin
                   FROM AdjustMarginItem AMI, FORM F
                   WHERE F.AKTime >= @enddate
                         AND AMI.MarginId = F.FORMID
                   GROUP BY ProjectId) ami
    ON P.ProjectId = ami.ProjectId
  LEFT OUTER JOIN (SELECT ProjectId,
                     SUM(OtherOutInAmount) OtherOutInAmount,
                     SUM(OtherOutInCost) OtherOutInCost
                   FROM (SELECT A.ProjectId,
                           SUM(Fi.BookAmount) OtherOutInAmount,
                           SUM(Fi.SellIncome) OtherOutInCost
                         FROM form                  F,
                           FormItem              Fi,
                           AssistantCarryForward A
                         WHERE F.FormId = FI.FormId
                               AND F.FormId = A.FormId
                               AND F.FormType NOT IN ('36', '37')
                               AND F.AKTime >= @enddate
                               AND FI.BookId = A.BookId
                         GROUP BY A.ProjectId
                         UNION ALL
                         SELECT A.ProjectId,
                           -SUM(Fi.BookAmount) OtherInAmount,
                           -SUM(Fi.SellIncome) OtherInCost
                         FROM form                  F,
                           FormItem              Fi,
                           AssistantCarryForward A
                         WHERE F.FormId = FI.FormId
                               AND F.FormId = A.FormId
                               AND F.FormType IN ('36', '37')
                               AND F.AKTime >= @enddate
                               AND FI.BookId = A.BookId
                         GROUP BY A.ProjectId
                         UNION ALL
                         SELECT rfi.OutProjectId AS ProjectId,
                                SUM(rfi.OutAmount) OtherInAmount,
                                SUM(rfi.OutWaitCarryForwordCost) OtherInCost
                         FROM form F, RefitItem rfi
                         WHERE F.FormId = rfi.RefitId
                               AND F.AKTime >= @enddate
                         GROUP BY rfi.OutProjectId) t
                   GROUP BY ProjectId) ooi
    ON P.ProjectId = ooi.ProjectId
  LEFT OUTER JOIN (SELECT ProjectId,
                     SUM(CarryForwordAmount) CarryForwordAmount,
                     SUM(CarryForwordCost) CarryForwordCost
                   FROM (SELECT ProjectId,
                           SUM(ISNULL(CFAmount, 0)) CarryForwordAmount,
                           SUM(ISNULL(CFCost, 0)) CarryForwordCost
                         FROM CarryForwardProject CFP, Form F
                         WHERE F.AKTime >= @enddate
                               AND CFP.CarryForwardId = F.FormId
                         GROUP BY ProjectId
                         UNION ALL
                         SELECT ProjectId,
                           SUM(ISNULL(CarryForwardAmount, 0)) CarryForwordAmount,
                           SUM(ISNULL(CarryForwardCost, 0)) CarryForwordCost
                         FROM CarryForwardStart cfs, Form F
                         WHERE F.AKTime >= @enddate
                               AND cfs.CarryForwardId = F.FormId
                         GROUP BY ProjectId) C
                   GROUP BY ProjectId) cfc
    ON P.ProjectId = cfc.ProjectId
  LEFT OUTER JOIN (SELECT POF.ProjectId,
                     SUM(CASE CG.DisplayName
                         WHEN '稿费/校订费/审稿费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 稿费 / 校订费 / 审稿费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '租型费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 租型费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '纸张费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 纸张费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '装帧纸张费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 装帧纸张费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '制版费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 制版费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '印刷费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 印刷费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '装订费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 装订费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '其他直接费用' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 其他直接费用 ],
                     SUM(CASE CG.DisplayName
                         WHEN '编录经费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 编录经费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '管理费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 管理费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '财务费' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 财务费 ],
                     SUM(CASE CG.DisplayName
                         WHEN '销售费用' THEN
                           ROUND(ISNULL(CheckedFee, 0), 2)
                         ELSE
                           0.00
                         END) [ 销售费用 ]
                   FROM ProjectOccuredFee POF,
                     (select BalanceItemId, Costproperty
                      from balancetypeanditems) T,
                     (SELECT StoredValue,
                        cast(DisplayName as varchar(24)) DisplayName
                      FROM CONSTGROUPSET
                      WHERE GROUPID = 170) AS CG
                   WHERE T.Costproperty = CG.StoredValue
                         AND POF.BalanceItemId = T.BalanceItemId
                   GROUP BY POF.ProjectId) AS FEE
    ON P.ProjectId = FEE.ProjectId
WHERE ((ISNULL(CA.WaitCFTotal, 0) - ISNULL(PCI.CheckInAmount, 0) +
        ISNULL(CFC.CarryForwordAmount, 0) +
        ISNULL(OOI.OtherOutInAmount, 0)) <> 0 OR
       (ISNULL(CA.CFPrintCost, 0) + ISNULL(CA.AdjustStorageCost, 0) -
        ISNULL(CA.OtehrInOutCost, 0) - ISNULL(CA.CarryForwordCost, 0) -
        ISNULL(PCI.CheckInCost, 0) - ISNULL(AMI.StorageCostMargin, 0) +
        ISNULL(CFC.CarryForwordCost, 0) + ISNULL(OOI.OtherOutInCost, 0)) <> 0)
      AND P.ProjectId IN (
  SELECT ProjectId FROM Project WHERE ProjectProperty<>7
  UNION
  SELECT pt.ProjectId FROM Project pt INNER JOIN PrintersBalance pb ON pt.ProjectId = pb.ProjectId
  WHERE pt.ProjectProperty= 7 AND pb.MerchandiseFirstState= 0x2c
)
      AND P.ProjectState NOT IN (5,7,8)
